﻿using BwTMusicHelper;
using BwTMusicModels;
using HwjUIDI.Utility.DatabaseHelper;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BwTMusicServices.Music
{
    public class MusicServices
    {
        public List<MusicHelper> GetAllNorthMusic(int CurrentPage,string CountryId, out int total)
        {
            List<MusicHelper> Lists = new List<MusicHelper>();
            total = 0;
            try
            {
                    if (CurrentPage > 0)
                    {
                        StringBuilder sbSQL = new StringBuilder();
                        List<SqlParameter> listParam = new List<SqlParameter>();
                        sbSQL.Append("select top 25");
                        sbSQL.Append($" * from (select count(1) over() as totalnumber, ROW_NUMBER() over(order by A.MusicId) as rownumber,A.MusicId ,A.MusicName,B.SingerName,B.SingerId,A.MusicSinger,A.MusicImgUrl,A.MusicBitRate,A.MusicLong,E.AlbumName,E.AlbumId from North_Music A left join North_Singer B on A.MusicSinger=B.SingerId left join North_Album E on A.MusicAlbum=E.AlbumId where A.IsVaild= 'Y' and B.SingerCountry= '{CountryId}' ");
                        sbSQL.Append(") WTX where rownumber > (");
                        sbSQL.Append((CurrentPage - 1) * 25);
                        sbSQL.Append(")");
                        using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sbSQL.ToString(), listParam.ToArray()))
                        {
                            while (dr.Read())
                            {
                                int iIndex = 0;
                                MusicHelper list = new MusicHelper();
                                list.Musics = new ModelsHelper().GetNorthMusicModels(dr);
                                list.Singer = new ModelsHelper().GetNorthSingerModels(dr);
                                list.Albums = new ModelsHelper().GetNorthAlbumModels(dr);
                                Lists.Add(list);
                                if (iIndex == 0)
                                {
                                    total = DatabaseFactory.GetDatabase().GetInt(dr, "totalnumber");
                                }
                                iIndex++;
                            }
                        }
                    }
            }
            catch (Exception ex)
            {

            }
            return Lists;
        }
        public List<MusicHelper> GetAllNorthMusicForType(int CurrentPage, string GenreId,string ThemeId, out int total)
        {
            List<MusicHelper> Lists = new List<MusicHelper>();
            total = 0;
            try
            {
                if (CurrentPage > 0)
                {
                    StringBuilder sbSQL = new StringBuilder();
                    List<SqlParameter> listParam = new List<SqlParameter>();
                    sbSQL.Append("select top 25");
                    sbSQL.Append($" * from (select count(1) over() as totalnumber, ROW_NUMBER() over(order by A.MusicId) as rownumber,A.MusicId ,A.MusicName,B.SingerName,B.SingerId,A.MusicSinger,A.MusicImgUrl,A.MusicBitRate,A.MusicLong,E.AlbumName,E.AlbumId from North_Music A left join North_Singer B on A.MusicSinger=B.SingerId left join North_Album E on A.MusicAlbum=E.AlbumId where A.IsVaild= 'Y' ");
                    if (!string.IsNullOrEmpty(GenreId))
                    {
                        sbSQL.Append($"and A.MusicTag= '{GenreId}' ");
                    }
                    if (!string.IsNullOrEmpty(ThemeId))
                    {
                        sbSQL.Append($"and A.MusicCategory= '{ThemeId}' ");
                    }
                    sbSQL.Append(") WTX where rownumber > (");
                    sbSQL.Append((CurrentPage - 1) * 25);
                    sbSQL.Append(")");
                    using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sbSQL.ToString(), listParam.ToArray()))
                    {
                        while (dr.Read())
                        {
                            int iIndex = 0;
                            MusicHelper list = new MusicHelper();
                            list.Musics = new ModelsHelper().GetNorthMusicModels(dr);
                            list.Singer = new ModelsHelper().GetNorthSingerModels(dr);
                            list.Albums = new ModelsHelper().GetNorthAlbumModels(dr);
                            Lists.Add(list);
                            if (iIndex == 0)
                            {
                                total = DatabaseFactory.GetDatabase().GetInt(dr, "totalnumber");
                            }
                            iIndex++;
                        }
                    }
                }
            }
            catch (Exception ex)
            {

            }
            return Lists;
        }
        public List<MusicHelper> GetAllNorthMusicForKey(int CurrentPage, string key, out int total)
        {
            List<MusicHelper> Lists = new List<MusicHelper>();
            total = 0;
            try
            {
                if (CurrentPage > 0)
                {
                    StringBuilder sbSQL = new StringBuilder();
                    List<SqlParameter> listParam = new List<SqlParameter>();
                    sbSQL.Append("select top 15");
                    sbSQL.Append($" * from (select count(1) over() as totalnumber, ROW_NUMBER() over(order by A.MusicId) as rownumber,A.MusicId ,A.MusicName,B.SingerId,B.SingerName,A.MusicSinger,A.MusicImgUrl,A.MusicBitRate,A.MusicLong,E.AlbumName,E.AlbumId from North_Music A left join North_Singer B on A.MusicSinger=B.SingerId left join North_Album E on A.MusicAlbum=E.AlbumId where A.IsVaild= 'Y' and  A.MusicName like '%{key}%'");
                    sbSQL.Append(") WTX where rownumber > (");
                    sbSQL.Append((CurrentPage - 1) * 15);
                    sbSQL.Append(")");
                    using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sbSQL.ToString(), listParam.ToArray()))
                    {
                        while (dr.Read())
                        {
                            int iIndex = 0;
                            MusicHelper list = new MusicHelper();
                            list.Musics = new ModelsHelper().GetNorthMusicModels(dr);
                            list.Singer = new ModelsHelper().GetNorthSingerModels(dr);
                            list.Albums = new ModelsHelper().GetNorthAlbumModels(dr);
                            Lists.Add(list);
                            if (iIndex == 0)
                            {
                                total = DatabaseFactory.GetDatabase().GetInt(dr, "totalnumber");
                            }
                            iIndex++;
                        }
                    }
                }
            }
            catch (Exception ex)
            {

            }
            return Lists;
        }
        public List<AlbumHelper> GetAllNorthAlbumForKey(int CurrentPage, string key, out int total)
        {
            List<AlbumHelper> Lists = new List<AlbumHelper>();
            total = 0;
            try
            {
                if (CurrentPage > 0)
                {
                    StringBuilder sbSQL = new StringBuilder();
                    List<SqlParameter> listParam = new List<SqlParameter>();
                    sbSQL.Append("select top 10");
                    sbSQL.Append($" * from (select count(1) over() as totalnumber, ROW_NUMBER() over(order by A.AlbumId) as rownumber,A.AlbumId ,A.AlbumName,B.SingerName,A.AlbumPic,A.AlbumTime from North_Album A left join SingerAlbum C on A.AlbumId=C.AlbumId left join North_Singer B on C.SingerId=B.SingerId  where  A.AlbumName like '%{key}%' ");
                    sbSQL.Append(") WTX where rownumber > (");
                    sbSQL.Append((CurrentPage - 1) * 10);
                    sbSQL.Append(")");
                    using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sbSQL.ToString(), listParam.ToArray()))
                    {
                        while (dr.Read())
                        {
                            int iIndex = 0;
                            AlbumHelper list = new AlbumHelper();
                            list.Album=  new ModelsHelper().GetNorthAlbumModels(dr);
                            list.Singer = new ModelsHelper().GetNorthSingerModels(dr);
                            Lists.Add(list);
                            if (iIndex == 0)
                            {
                                total = DatabaseFactory.GetDatabase().GetInt(dr, "totalnumber");
                            }
                            iIndex++;
                        }
                    }
                }
            }
            catch (Exception ex)
            {

            }
            Lists = Lists.Distinct(o => o.Album.AlbumId).ToList();           
            return Lists;
        }
        public int AddMusicClickCount(int id)
        {
            int bResult = 0;
            try
            {
                string sql = $"update North_Music set MusicClickCount=MusicClickCount+1 where MusicId='{id}'";
                bResult = DatabaseFactory.GetDatabase().ExecNonQuerySQL(sql);
            }
            catch (Exception)
            {
               
            }         
            return bResult;
        }
        public int AddMusicChooseCount(int id)
        {
            int bResult = 0;
            try
            {
                string sql = $"update North_Music set MusicClickCount=MusicChooseCount+1 where MusicId='{id}'";
                bResult = DatabaseFactory.GetDatabase().ExecNonQuerySQL(sql);
            }
            catch (Exception)
            {

            }
            return bResult;
        }
        public List<MusicHelper> GetAllNorthSingerMusic(int CurrentPage, int singerId, out int total)
        {
            List<MusicHelper> Lists = new List<MusicHelper>();
            total = 0;
            try
            {
                if (CurrentPage > 0)
                {
                    StringBuilder sbSQL = new StringBuilder();
                    sbSQL.Append("select top 15");
                    sbSQL.Append($" * from (select count(1) over() as totalnumber, ROW_NUMBER() over(order by A.MusicId) as rownumber,A.MusicId ,A.MusicName,A.MusicSinger,A.MusicImgUrl,A.MusicBitRate,A.MusicLong,B.AlbumName,B.AlbumId from North_Music A  left join North_Album  B on A.MusicAlbum = B.AlbumId where A.IsVaild= 'Y' and A.MusicSinger= '{singerId}' ");
                    sbSQL.Append(") WTX where rownumber > (");
                    sbSQL.Append((CurrentPage - 1) * 15);
                    sbSQL.Append(")");
                    using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sbSQL.ToString()))
                    {
                        while (dr.Read())
                        {
                            int iIndex = 0;
                            MusicHelper list = new MusicHelper();
                            list.Musics = new ModelsHelper().GetNorthMusicModels(dr);
                            list.Albums = new ModelsHelper().GetNorthAlbumModels(dr);
                            Lists.Add(list);
                            if (iIndex == 0)
                            {
                                total = DatabaseFactory.GetDatabase().GetInt(dr, "totalnumber");
                            }
                            iIndex++;
                        }
                    }
                }
            }
            catch (Exception ex)
            {

            }
            return Lists;
        }


        public List<North_Album> GetAllNorthSingerAlbum(int CurrentPage, int singerId, out int total)
        {
            List<North_Album> Lists = new List<North_Album>();
            total = 0;
            try
            {
                if (CurrentPage > 0)
                {
                    StringBuilder sbSQL = new StringBuilder();
                    sbSQL.Append("select top 15");
                    sbSQL.Append($" * from (select count(1) over() as totalnumber, ROW_NUMBER() over(order by A.AlbumId) as rownumber,A.AlbumId ,A.AlbumName,A.AlbumPic,A.AlbumTime from North_Album A  left join SingerAlbum  B on A.AlbumId = B.AlbumId where  B.SingerId= '{singerId}' ");
                    sbSQL.Append(") WTX where rownumber > (");
                    sbSQL.Append((CurrentPage - 1) * 15);
                    sbSQL.Append(")");
                    using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sbSQL.ToString()))
                    {
                        while (dr.Read())
                        {
                            int iIndex = 0;
                            North_Album list = new North_Album();
                            list = new ModelsHelper().GetNorthAlbumModels(dr);
                            Lists.Add(list);
                            if (iIndex == 0)
                            {
                                total = DatabaseFactory.GetDatabase().GetInt(dr, "totalnumber");
                            }
                            iIndex++;
                        }
                    }
                }
            }
            catch (Exception ex)
            {

            }
            return Lists;
        }
    }
}
